package com.yuanren.em.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yuanren.em.entity.MemberCoupon;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * 会员优惠券Mapper接口
 */
public interface MemberCouponMapper extends BaseMapper<MemberCoupon> {

    /**
     * 分页查询会员优惠券（带用户信息）
     */
    @Select("SELECT c.*, u.username, u.name as user_name " +
            "FROM t_member_coupon c " +
            "LEFT JOIN sys_user u ON c.user_id = u.id " +
            "WHERE (#{userId} IS NULL OR c.user_id = #{userId}) " +
            "AND (#{couponType} IS NULL OR c.coupon_type = #{couponType}) " +
            "AND (#{isUsed} IS NULL OR c.is_used = #{isUsed}) " +
            "AND (#{source} IS NULL OR c.source = #{source}) " +
            "ORDER BY c.created_at DESC")
    IPage<MemberCoupon> selectCouponPageWithInfo(Page<MemberCoupon> page,
                                                 @Param("userId") Long userId,
                                                 @Param("couponType") String couponType,
                                                 @Param("isUsed") Boolean isUsed,
                                                 @Param("source") String source);

    /**
     * 根据用户ID查询可用优惠券
     */
    @Select("SELECT * FROM t_member_coupon " +
            "WHERE user_id = #{userId} " +
            "AND is_used = false " +
            "AND expire_date > NOW() " +
            "ORDER BY expire_date ASC")
    List<MemberCoupon> selectAvailableCouponsByUserId(@Param("userId") Long userId);

    /**
     * 根据用户ID和订单金额查询可用优惠券
     */
    @Select("SELECT * FROM t_member_coupon " +
            "WHERE user_id = #{userId} " +
            "AND is_used = false " +
            "AND expire_date > NOW() " +
            "AND (min_order_amount IS NULL OR min_order_amount <= #{orderAmount}) " +
            "ORDER BY " +
            "CASE " +
            "    WHEN coupon_type = 'AMOUNT' THEN discount_amount " +
            "    WHEN coupon_type = 'DISCOUNT' THEN #{orderAmount} * discount_rate / 100 " +
            "    ELSE 0 " +
            "END DESC")
    List<MemberCoupon> selectApplicableCoupons(@Param("userId") Long userId, 
                                               @Param("orderAmount") BigDecimal orderAmount);

    /**
     * 根据优惠券码查询优惠券
     */
    @Select("SELECT c.*, u.username, u.name as user_name " +
            "FROM t_member_coupon c " +
            "LEFT JOIN sys_user u ON c.user_id = u.id " +
            "WHERE c.coupon_code = #{couponCode}")
    MemberCoupon selectByCouponCode(@Param("couponCode") String couponCode);

    /**
     * 统计用户优惠券情况
     */
    @Select("SELECT " +
            "COUNT(*) as total_coupons, " +
            "SUM(CASE WHEN is_used = false AND expire_date > NOW() THEN 1 ELSE 0 END) as available_coupons, " +
            "SUM(CASE WHEN is_used = true THEN 1 ELSE 0 END) as used_coupons, " +
            "SUM(CASE WHEN is_used = false AND expire_date <= NOW() THEN 1 ELSE 0 END) as expired_coupons " +
            "FROM t_member_coupon " +
            "WHERE user_id = #{userId}")
    Map<String, Object> selectUserCouponStats(@Param("userId") Long userId);

    /**
     * 统计总体优惠券情况
     */
    @Select("SELECT " +
            "COUNT(*) as total_coupons, " +
            "COUNT(DISTINCT user_id) as unique_users, " +
            "SUM(CASE WHEN is_used = true THEN 1 ELSE 0 END) as used_coupons, " +
            "SUM(CASE WHEN is_used = false AND expire_date > NOW() THEN 1 ELSE 0 END) as active_coupons " +
            "FROM t_member_coupon " +
            "WHERE DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)")
    Map<String, Object> selectCouponStats();

    /**
     * 根据来源统计优惠券
     */
    @Select("SELECT source, COUNT(*) as count, " +
            "SUM(CASE WHEN is_used = true THEN 1 ELSE 0 END) as used_count " +
            "FROM t_member_coupon " +
            "WHERE DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
            "GROUP BY source")
    List<Map<String, Object>> selectCouponCountBySource();

    /**
     * 查询即将过期的优惠券
     */
    @Select("SELECT c.*, u.username, u.name as user_name " +
            "FROM t_member_coupon c " +
            "LEFT JOIN sys_user u ON c.user_id = u.id " +
            "WHERE c.is_used = false " +
            "AND c.expire_date > NOW() " +
            "AND c.expire_date <= DATE_ADD(NOW(), INTERVAL #{days} DAY) " +
            "ORDER BY c.expire_date ASC")
    List<MemberCoupon> selectExpiringCoupons(@Param("days") Integer days);

    /**
     * 检查优惠券码是否存在
     */
    @Select("SELECT COUNT(*) FROM t_member_coupon WHERE coupon_code = #{couponCode}")
    Integer countByCouponCode(@Param("couponCode") String couponCode);
} 